import mysql.connector
from mysql.connector import errorcode
import os
from dotenv import load_dotenv

load_dotenv()

# 数据库配置信息，从环境变量中读取
DB_NAME = os.getenv('DB_NAME')
db_config = {
    'user': os.getenv('DB_USER'),
    'password': os.getenv('DB_PASSWORD'),
    'host': os.getenv('DB_HOST')
}

# 定义所有的表格及其创建语句
TABLES = {}

TABLES['users'] = """
CREATE TABLE IF NOT EXISTS `users` (
    `user_id` BIGINT AUTO_INCREMENT PRIMARY KEY,
    `username` VARCHAR(100) NOT NULL UNIQUE,
    `email` VARCHAR(100) NOT NULL UNIQUE,
    `password_hash` VARCHAR(255) NOT NULL,
    `role` ENUM('parent', 'child') NOT NULL,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
"""

TABLES['parent_child'] = """
CREATE TABLE IF NOT EXISTS `parent_child` (
    `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
    `parent_id` BIGINT,
    `child_id` BIGINT,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`parent_id`) REFERENCES `users`(`user_id`) ON DELETE CASCADE,
    FOREIGN KEY (`child_id`) REFERENCES `users`(`user_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
"""

TABLES['games'] = """
CREATE TABLE IF NOT EXISTS `games` (
    `game_id` BIGINT AUTO_INCREMENT PRIMARY KEY,
    `parent_id` BIGINT,
    `title` VARCHAR(255) NOT NULL UNIQUE,
    `description` TEXT,
    `category` VARCHAR(100),
    `mode` ENUM('spell', 'translate') NOT NULL,
    `duration` INT DEFAULT 300,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`parent_id`) REFERENCES `users`(`user_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
"""

TABLES['game_wordlist'] = """
CREATE TABLE IF NOT EXISTS `game_wordlist` (
    `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
    `game_id` BIGINT,
    `word_id` BIGINT,
    `sequence` INT NOT NULL,
    FOREIGN KEY (`game_id`) REFERENCES `games`(`game_id`) ON DELETE CASCADE,
    FOREIGN KEY (`word_id`) REFERENCES `wordlist`(`word_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
"""

TABLES['wordlist'] = """
CREATE TABLE IF NOT EXISTS `wordlist` (
    `word_id` BIGINT AUTO_INCREMENT PRIMARY KEY,
    `english` VARCHAR(100) NOT NULL UNIQUE,
    `chinese` VARCHAR(255) NOT NULL,
    `phonetic` VARCHAR(100),
    `audio` VARCHAR(255),
    `example` TEXT,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
"""

TABLES['game_records'] = """
CREATE TABLE IF NOT EXISTS `game_records` (
    `record_id` BIGINT AUTO_INCREMENT PRIMARY KEY,
    `game_id` BIGINT,
    `game_mode` ENUM('spell', 'translate') NOT NULL,
    `user_id` BIGINT,
    `score` INT DEFAULT 0,
    `completion_rate` DECIMAL(5, 2) DEFAULT 0.00,
    `status` ENUM('not_started', 'in_progress', 'completed') DEFAULT 'not_started',
    `completed_at` DATETIME,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`game_id`) REFERENCES `games`(`game_id`) ON DELETE CASCADE,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`user_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
"""

TABLES['game_shares'] = """
CREATE TABLE IF NOT EXISTS `game_shares` (
    `share_id` BIGINT AUTO_INCREMENT PRIMARY KEY,
    `game_id` BIGINT,
    `parent_id` BIGINT,
    `child_id` BIGINT,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`game_id`) REFERENCES `games`(`game_id`) ON DELETE CASCADE,
    FOREIGN KEY (`parent_id`) REFERENCES `users`(`user_id`) ON DELETE CASCADE,
    FOREIGN KEY (`child_id`) REFERENCES `users`(`user_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
"""

TABLES['word_learning_records'] = """
CREATE TABLE IF NOT EXISTS `word_learning_records` (
    `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
    `user_id` BIGINT NOT NULL,
    `word_id` BIGINT NOT NULL,
    `spell_correct_count` INT DEFAULT 0,
    `spell_wrong_count` INT DEFAULT 0,
    `translate_correct_count` INT DEFAULT 0,
    `translate_wrong_count` INT DEFAULT 0,
    `last_reviewed_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`user_id`),
    FOREIGN KEY (`word_id`) REFERENCES `wordlist`(`word_id`),
    UNIQUE KEY `unique_user_word` (`user_id`, `word_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
"""

TABLES['user_scores'] = """
CREATE TABLE IF NOT EXISTS `user_scores` (
    `user_id` BIGINT PRIMARY KEY,
    `score` INT DEFAULT 0,
    `games` INT DEFAULT 0,
    `spell_count` INT DEFAULT 0,
    `trans_count` INT DEFAULT 0,
    `other_count` INT DEFAULT 0,
    `avg_rate` DECIMAL(5, 2) DEFAULT 0.00,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`user_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
"""


def create_database(cursor):
    """创建 BunnySpell 数据库"""
    try:
        cursor.execute(f"CREATE DATABASE {DB_NAME} DEFAULT CHARACTER SET 'utf8mb4'")
        print(f"Database `{DB_NAME}` created successfully.")
    except mysql.connector.Error as err:
        print(f"Failed creating database: {err}")
        exit(1)


def create_tables(cursor):
    """创建所有的表格"""
    cursor.execute(f"USE {DB_NAME}")
    for table_name, ddl in TABLES.items():
        try:
            print(f"Creating table `{table_name}`: ", end='')
            cursor.execute(ddl)
            print("OK")
        except mysql.connector.Error as err:
            if err.errno == errorcode.ER_TABLE_EXISTS_ERROR:
                print("already exists.")
            else:
                print(err.msg)


def main():
    # 连接 MySQL 数据库
    try:
        conn = mysql.connector.connect(**db_config)
        cursor = conn.cursor()
        print("Connected to MySQL database.")
    except mysql.connector.Error as err:
        print(f"Error: {err}")
        exit(1)

    # 创建数据库
    try:
        cursor.execute(f"USE {DB_NAME}")
    except mysql.connector.Error as err:
        if err.errno == errorcode.ER_BAD_DB_ERROR:
            create_database(cursor)
            cursor.execute(f"USE {DB_NAME}")
        else:
            print(f"Error: {err}")
            exit(1)

    # 创建所有表格
    create_tables(cursor)

    # 关闭连接
    cursor.close()
    conn.close()
    print("All tables created successfully.")


if __name__ == "__main__":
    main()
